<?php

namespace app\system\controller\customer;

use app\common\controller\System;
use app\system\controller\daily\Throwable;
use hehe\Arr;
use hehe\Str;
use hehe\Trade;
use PhpOffice\PhpSpreadsheet\RichText\RichText;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use think\facade\Config;
use think\facade\Db;
use think\facade\Lang;
use ba\Filesystem;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;


class Generate extends System {
    /**
     * Goods模型对象
     * @var object
     */
    protected object $model;

    protected string|array $defaultSortField = 'id,desc';

    protected array|string $preExcludeFields = ['id', 'create_time'];

    protected string|array $quickSearchField = ['name', 'describe'];

    protected string $goodsField = 'g.*, h.hscode';

    public function initialize(): void {
        parent::initialize();
    }

    /**
     * 生成文件
     */
    public function generateFile(){
        $params = $this->request->param();
        $dir = Filesystem::fsFit(public_path() . 'storage\\customer_order\\');
        if(!file_exists($dir)){
            Filesystem::mkdir($dir);
        }
        $order = db::name('customer_order')->where(['id' => $params['order_id']])->order('id asc')->find();
        $customer = db::name('customer')->where(['id' => $order['customer_id']])->find();

        $shipment = Config::get('options.shipment.' . $customer['id']);


        if(empty($order['shipment'])){
            $this->error('请设置该笔订单的运输方式');
        }
        if(empty($order['exchange'])){
            $this->error('请设置该笔订单的汇率');
        }

        $order_list = db::name('customer_order')->where(['out_trade_no' => $order['out_trade_no']])->select()->toArray();
        $order_ids = array_column($order_list, 'id');
        $order_detail = db::name('customer_order_detail')->whereIn('order_id', $order_ids)->select()->toArray();
        $quantity = [];
        foreach($order_detail as $val){
            if(empty($quantity[$val['product_id']])){
                $quantity[$val['product_id']] = $val['quantity'] / $this->multiply;
            }else{
                $quantity[$val['product_id']] += $val['quantity'] / $this->multiply;
            }

        }
        $product_ids = array_column($order_detail, 'product_id');
        $product = db::name('product')->whereIn('id', $product_ids)->select()->toArray();
        $category_ids = array_column($product, 'category_id');
        $category = db::name('product_category')->whereIn('id', $category_ids)->select()->toArray();
        $item = [];
        $product_name = [];
        foreach($category as $key => $val){
            $product_name[] = $val['name_cn'];
            $item[$key]['category'] = $val;
            $item[$key]['product'] = [];
            foreach($product as $k => $v){
                if($val['id'] == $v['category_id']){
                    $item[$key]['product'][] = $v;
                }
            }
        }
        $product_count = count($quantity);

        // 报价单
        if($params['type'] == 'quotation'){
            // 加载Excel文件
            $spreadsheet = IOFactory::load(Filesystem::fsFit(public_path() . 'static\\file_template\\quotation.xlsx'));
            // 获取工作表
            $sheet = $spreadsheet->getSheet(0);
            $sheet->setCellValue('B5', $shipment['company_name']);
            $sheet->setCellValue('E5', date('y/m/d', $order['create_time']));
            $sheet->setCellValue('B6', $shipment['address']);
            $sheet->setCellValue('E6', $order['contract']);
            $sheet->setCellValue('B7', $shipment['tel']);
            $sheet->setCellValue('E7', $order['exchange']);
            $sheet->setCellValue('B8', $shipment['email']);
            $sheet->setCellValue('E8', '');
            $sheet->setCellValue('A10', 'Shipment: BY ' . strtoupper($order['shipment'])); // 运输方式
            $sheet->setCellValue('A11', 'Destination : ' . $shipment['destination']); // 目的地



            $index = 18;

            if($product_count > 1){ // 插入行
                $sheet->insertNewRowBefore($index + 1, $product_count - 1);
            }
            $no = 1;
            foreach($item as $key => $val){
                foreach($val['product'] as $k => $v){
                    $sheet->setCellValue('A' . $index, $no); // 序号
                    $sheet->setCellValue('B' . $index, $v['name_customer']); // 产品名称
                    $sheet->setCellValue('C' . $index, $v['specification_customer']); // 产品规格
                    $sheet->setCellValue('D' . $index, $quantity[$v['id']]); // 下单数量
                    $sheet->setCellValue('E' . $index, $val['category']['unit']); // 单位

                    if($val['sale_currency'] = 'KRW'){ // 单价
                        $sheet->setCellValue('F' . $index, $v['sale_price'] / $this->multiply); // 单价
                    }else if($val['sale_currency'] == 'USD'){
                        $sheet->setCellValue('F' . $index, '=' . ($v['sale_price'] / $this->multiply) . '*E7'); // 单价
                    }else{
                        $sheet->setCellValue('F' . $index, 0); // 单价缺少计算条件
                    }
                    $sheet->setCellValue('G' . $index, '=D' . $index . '*F' . $index); // 总价
                    $no++;
                    $index++;
                }
            }
            $item_row = $index - 1;
            $sheet->setCellValue('D' . $index, '=SUM(D18:D' . $item_row . ')'); // 总数量
            $sheet->setCellValue('G' . $index, '=SUM(G18:G' . $item_row . ')'); // 总金额

            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); // 保存为Excel 2007及以上版本的文件
            $filename = 'QUOTATION(' . implode('&', $product_name) . ')-' . $order['contract'] . '.xlsx';
            $filepath = $dir . $filename;


            $writer->save($filepath); // 修改为你想保存的文件名

            $data = [
                'order_id' => $params['order_id'],
                'out_trade_no' => $order['out_trade_no'],
                'type' => 'customer_quotation',
                'file_name' => $filename,
                'file_link' => '/storage/customer_order/' . $filename,
                'create_time' => $this->timestamp,
            ];
            db::name('order_file')->insert($data);
            $this->success();

        }

        // 形式发票
        if($params['type'] == 'pi'){
            // 加载Excel文件
            $spreadsheet = IOFactory::load(Filesystem::fsFit(public_path() . 'static\\file_template\\pi.xlsx'));
            // 获取工作表
            $sheet = $spreadsheet->getSheet(0);
            $sheet->setCellValue('B5', $shipment['company_name']);
            $sheet->setCellValue('E5', date('y/m/d', $order['create_time']));
            $sheet->setCellValue('B6', $shipment['address']);
            $sheet->setCellValue('E6', $order['contract']);
            $sheet->setCellValue('B7', $shipment['tel']);
            $sheet->setCellValue('E7', $order['exchange']);
            $sheet->setCellValue('B8', $shipment['email']);
            $sheet->setCellValue('E8', '');
            $sheet->setCellValue('A10', 'Shipment: BY ' . strtoupper($order['shipment'])); // 运输方式
            $sheet->setCellValue('A11', 'Destination : ' . $shipment['destination']); // 目的地



            $index = 18;

            if($product_count > 1){ // 插入行
                $sheet->insertNewRowBefore($index + 1, $product_count - 1);
            }
            $no = 1;
            foreach($item as $key => $val){
                foreach($val['product'] as $k => $v){
                    $sheet->setCellValue('A' . $index, $no); // 序号
                    $sheet->setCellValue('B' . $index, $v['name_customer']); // 产品名称
                    $sheet->setCellValue('C' . $index, $v['specification_customer']); // 产品规格
                    $sheet->setCellValue('D' . $index, $quantity[$v['id']]); // 下单数量
                    $sheet->setCellValue('E' . $index, $val['category']['unit']); // 单位

                    if($val['sale_currency'] = 'KRW'){ // 单价
                        $sheet->setCellValue('F' . $index, '=' . ($v['sale_price'] / $this->multiply) . '/E7'); // 单价
                    }else{
                        $sheet->setCellValue('F' . $index, $v['sale_price'] / $this->multiply); // 单价
                    }
                    $sheet->setCellValue('G' . $index, '=D' . $index . '*F' . $index); // 总价
                    $no++;
                    $index++;
                }
            }
            $item_row = $index - 1;
            $sheet->setCellValue('D' . $index, '=SUM(D18:D' . $item_row . ')'); // 总数量
            $sheet->setCellValue('G' . $index, '=SUM(G18:G' . $item_row . ')'); // 总金额

            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); // 保存为Excel 2007及以上版本的文件
            $filename = 'PI(' . implode('&', $product_name) . ')-' . $order['contract'] . '.xlsx';
            $filepath = $dir . $filename;
            $writer->save($filepath); // 修改为你想保存的文件名

            $data = [
                'order_id' => $params['order_id'],
                'out_trade_no' => $order['out_trade_no'],
                'type' => 'customer_pi',
                'file_name' => $filename,
                'file_link' => '/storage/customer_order/' . $filename,
                'create_time' => $this->timestamp,
            ];
            db::name('order_file')->insert($data);
            $this->success();

        }

        // 商业发票
        if($params['type'] == 'ci'){
            // 加载Excel文件
            $spreadsheet = IOFactory::load(Filesystem::fsFit(public_path() . 'static\\file_template\\ci.xlsx'));
            // 获取工作表
            $sheet = $spreadsheet->getSheet(0);
            $sheet->setCellValue('B5', $shipment['company_name']);
            $sheet->setCellValue('E5', date('y/m/d', $order['create_time']));
            $sheet->setCellValue('B6', $shipment['address']);
            $sheet->setCellValue('E6', $order['contract']);
            $sheet->setCellValue('B7', $shipment['tel']);
            $sheet->setCellValue('E7', $order['exchange']);
            $sheet->setCellValue('B8', $shipment['email']);
            $sheet->setCellValue('E8', '');
            $sheet->setCellValue('A10', 'Shipment: BY ' . strtoupper($order['shipment'])); // 运输方式
            $sheet->setCellValue('A11', 'Destination : ' . $shipment['destination']); // 目的地



            $index = 18;

            if($product_count > 1){ // 插入行
                $sheet->insertNewRowBefore($index + 1, $product_count - 1);
            }
            $no = 1;
            foreach($item as $key => $val){
                foreach($val['product'] as $k => $v){
                    $sheet->setCellValue('A' . $index, $no); // 序号
                    $sheet->setCellValue('B' . $index, $v['name_customer']); // 产品名称
                    $sheet->setCellValue('C' . $index, $v['specification_customer']); // 产品规格
                    $sheet->setCellValue('D' . $index, $quantity[$v['id']]); // 下单数量
                    $sheet->setCellValue('E' . $index, $val['category']['unit']); // 单位

                    if($val['sale_currency'] = 'KRW'){ // 单价
                        $sheet->setCellValue('F' . $index, '=' . ($v['sale_price'] / $this->multiply) . '/E7'); // 单价
                    }else{
                        $sheet->setCellValue('F' . $index, $v['sale_price'] / $this->multiply); // 单价
                    }
                    $sheet->setCellValue('G' . $index, '=D' . $index . '*F' . $index); // 总价
                    $no++;
                    $index++;
                }
            }
            $item_row = $index - 1;
            $sheet->setCellValue('D' . $index, '=SUM(D18:D' . $item_row . ')'); // 总数量
            $sheet->setCellValue('G' . $index, '=SUM(G18:G' . $item_row . ')'); // 总金额

            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); // 保存为Excel 2007及以上版本的文件
            $filename = 'CI(' . implode('&', $product_name) . ')-' . $order['contract'] . '.xlsx';
            $filepath = $dir . $filename;
            $writer->save($filepath); // 修改为你想保存的文件名

            $data = [
                'order_id' => $params['order_id'],
                'out_trade_no' => $order['out_trade_no'],
                'type' => 'customer_ci',
                'file_name' => $filename,
                'file_link' => '/storage/customer_order/' . $filename,
                'create_time' => $this->timestamp,
            ];
            db::name('order_file')->insert($data);
            $this->success();

        }

        // 箱单
        if($params['type'] == 'pl'){
            // 加载Excel文件
            $spreadsheet = IOFactory::load(Filesystem::fsFit(public_path() . 'static\\file_template\\pl.xlsx'));
            // 获取工作表
            $sheet = $spreadsheet->getSheet(0);
            $sheet->setCellValue('B6', $shipment['company_name']);
            $sheet->setCellValue('B7', $shipment['address']);
            $sheet->setCellValue('B8', $shipment['tel']);

            $sheet->setCellValue('A10', 'Invoice No.：' . $order['contract']);
            $sheet->setCellValue('A11', 'Date：' . date('y/m/d', $order['create_time']));
            $sheet->setCellValue('A12', 'Shipment：BY ' . strtoupper($order['shipment']) . ' TRANSPORT'); // 运输方式
            $sheet->setCellValue('A13', 'Destination：' . $shipment['destination']); // 目的地



            $index = 16;

            if($product_count > 1){ // 插入行
                $sheet->insertNewRowBefore($index + 1, $product_count - 1);
            }
            $no = 1;
            foreach($item as $key => $val){
                foreach($val['product'] as $k => $v){
                    $sheet->setCellValue('A' . $index, $no); // 序号
                    $sheet->setCellValue('B' . $index, $v['name_customer']); // 产品名称
                    $sheet->setCellValue('C' . $index, $v['specification_customer']); // 产品规格
                    $sheet->setCellValue('D' . $index, $quantity[$v['id']]); // 下单数量
                    $sheet->setCellValue('E' . $index, $val['category']['unit']); // 单位

                    $no++;
                    $index++;
                }
            }
            $item_row = $index - 1;
            $sheet->setCellValue('D' . $index, '=SUM(D18:D' . $item_row . ')'); // 总数量

            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); // 保存为Excel 2007及以上版本的文件
            $filename = 'PL(' . implode('&', $product_name) . ')-' . $order['contract'] . '.xlsx';
            $filepath = $dir . $filename;
            $writer->save($filepath); // 修改为你想保存的文件名

            $data = [
                'order_id' => $params['order_id'],
                'out_trade_no' => $order['out_trade_no'],
                'type' => 'customer_pl',
                'file_name' => $filename,
                'file_link' => '/storage/customer_order/' . $filename,
                'create_time' => $this->timestamp,
            ];
            db::name('order_file')->insert($data);
            $this->success();

        }



    }

    /**
     * 进口 - 物流 - 报关单
     */
    public function generateJkWlBgd($param, $dir){
        $information = db::name('customer_order_information')->where(['order_id' => $param['order_id']])->find();
        $goods = db::name('customer_order_goods')->where(['order_id' => $param['order_id']])->select();
        // 加载Excel文件
        $spreadsheet = IOFactory::load(Filesystem::fsFit(public_path() . 'static\\file_template\\进口-物流相关-报关单.xlsx'));
        // 获取工作表
        $sheet = $spreadsheet->getSheet(0);
        $index = 38;
        if(count($goods) > 1){
            //            $sheet->insertNewRowBefore($index + 1, count($goods) - 1);
        }
        $goods_name = "";
        foreach($goods as $val){
            //            $sheet->mergeCells("D{$index}:F{$index}");
            //            $sheet->setCellValue('D' . $index, $val['name']); // 名称
            $goods_name .= $val['name'] . '&';
            $index++;
        }
        $goods_name = rtrim($goods_name, '&');
        $sign_date = empty($information['sign_date']) ? '未设置签约日期' : date('ymd', strtotime($information['sign_date']));
        // 保存修改后的Excel文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); // 保存为Excel 2007及以上版本的文件
        $filename = '报关单(' . $goods_name . ') - ' . $sign_date . ' - 进口.xlsx';
        $filepath = $dir . $filename;
        $writer->save($filepath); // 修改为你想保存的文件名
        return $filename;
    }

    /**
     * 进口 - 物流相关 - 箱单
     */
    public function generateJkWlXd($param, $dir){
        $information = db::name('customer_order_information')->where(['order_id' => $param['order_id']])->find();
        $goods = db::name('customer_order_goods')->where(['order_id' => $param['order_id']])->select();
        // 加载Excel文件
        $spreadsheet = IOFactory::load(Filesystem::fsFit(public_path() . 'static\\file_template\\进口-物流相关-箱单.xlsx'));


        $sheet = $spreadsheet->getSheet(0);

        if(!empty($information['sign_date'])) {
            $sheet->setCellValue('B4', $information['sign_date']);
        }
        if(!empty($information['invoice_number'])) {
            $sheet->setCellValue('B5', $information['invoice_number']);
        }
        if(!empty($information['contract_number'])) {
            $sheet->setCellValue('B6', $information['contract_number']);
        }
        if(!empty($information['trade_country'])) {
            $sheet->setCellValue('G4', __($information['trade_country'], [], 'en')); // 装货港
        }
        $sheet->setCellValue('G5', 'China'); // 最终目的地
        $sheet->setCellValue('G6', '');


        $index = 21;
        if(count($goods) > 1){
            $sheet->insertNewRowBefore($index + 1, count($goods) - 1);
        }
        $goods_name = "";
        foreach($goods as $val){
            if(empty($val['hscode_id'])){
                $hscode_result = null;
            }else{
                $hscode_result = db::name('hscode')->where(['id' => $val['hscode_id']])->find();
            }
            if($hscode_result){
                $sheet->setCellValue('A' . $index, $hscode_result['hscode']);
            }else{
                $sheet->setCellValue('A' . $index, '');
            }
            $sheet->setCellValue('B' . $index, $val['name_en']);
            $sheet->setCellValue('C' . $index, ''); // 数量
            $sheet->setCellValue('D' . $index, ''); // 单位
            $goods_name .= $val['name'] . '&';
            $index++;
        }
        $sheet->setCellValue('C' . $index, ''); // 总数量
        $goods_name = rtrim($goods_name, '&');
        $sign_date = empty($information['sign_date']) ? '未设置签约日期' : date('ymd', strtotime($information['sign_date']));
        // 保存修改后的Excel文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); // 保存为Excel 2007及以上版本的文件
        $filename = '箱单(' . $goods_name . ') - ' . $sign_date . '.xlsx';
        $filepath = $dir . $filename;
        $writer->save($filepath); // 修改为你想保存的文件名
        return $filename;
    }

    /**
     * 进口 - 供应商 - 合同
     */
    public function generateJkGysHt($param, $dir){
        $information = db::name('customer_order_information')->where(['order_id' => $param['order_id']])->find();
        $goods = db::name('customer_order_goods')->where(['order_id' => $param['order_id']])->select();
        // 加载Excel文件
        $spreadsheet = IOFactory::load(Filesystem::fsFit(public_path() . 'static\\file_template\\进口-供应商-合同.xlsx'));
        // 获取工作表
        $sheet = $spreadsheet->getSheet(0);
        if(!empty($information['sign_date'])) {
            $sheet->setCellValue('G3', $information['sign_date']);
        }
        if(!empty($information['contract_number'])) {
            $sheet->setCellValue('G4', $information['contract_number']);
        }



        $index = 8;
        if(count($goods) > 1){
            $sheet->insertNewRowBefore($index + 1, count($goods) - 1);
        }
        $goods_name = "";
        foreach($goods as $val){
            $sheet->mergeCells("A{$index}:B{$index}");
            $sheet->mergeCells("C{$index}:D{$index}");
            $sheet->mergeCells("E{$index}:F{$index}");
            $sheet->mergeCells("G{$index}:H{$index}");
            $sheet->setCellValue('A' . $index, $val['name']); // 名称

            $goods_name .= $val['name'] . '&';
            $index++;
        }


        $goods_name = rtrim($goods_name, '&');
        $sign_date = empty($information['sign_date']) ? '未设置签约日期' : date('ymd', strtotime($information['sign_date']));
        // 保存修改后的Excel文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); // 保存为Excel 2007及以上版本的文件
        $filename = '供应商合同(' . $goods_name . ') - ' . $sign_date . '.xlsx';
        $filepath = $dir . $filename;
        $writer->save($filepath); // 修改为你想保存的文件名
        return $filename;
    }

    /**
     * 进口 - 客户 - 合同
     */
    public function generateJkKhHt($param, $dir){
        $information = db::name('customer_order_information')->where(['order_id' => $param['order_id']])->find();
        $goods = db::name('customer_order_goods')->where(['order_id' => $param['order_id']])->select();
        // 加载Excel文件
        $spreadsheet = IOFactory::load(Filesystem::fsFit(public_path() . 'static\\file_template\\进口-客户-合同.xlsx'));
        // 获取工作表
        $sheet = $spreadsheet->getSheet(0);
        $index = 8;
        if(count($goods) > 1){
            $sheet->insertNewRowBefore($index + 1, count($goods) - 1);
        }
        $goods_name = "";
        foreach($goods as $val){
//            $sheet->mergeCells("D{$index}:F{$index}");
            $sheet->setCellValue('A' . $index, $val['name']); // 名称
            $goods_name .= $val['name'] . '&';
            $index++;
        }
        $goods_name = rtrim($goods_name, '&');
        $sign_date = empty($information['sign_date']) ? '未设置签约日期' : date('ymd', strtotime($information['sign_date']));
        // 保存修改后的Excel文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); // 保存为Excel 2007及以上版本的文件
        $filename = '客户合同(' . $goods_name . ') - ' . $sign_date . '.xlsx';
        $filepath = $dir . $filename;
        $writer->save($filepath); // 修改为你想保存的文件名
        return $filename;
    }

    /**
     * 出口 - 物流相关 - 报关单
     */
    public function generateCkWlBgd($param, $dir){
        $information = db::name('customer_order_information')->where(['order_id' => $param['order_id']])->find();
        $goods = db::name('customer_order_goods')->where(['order_id' => $param['order_id']])->select();
        // 加载Excel文件
        $spreadsheet = IOFactory::load(Filesystem::fsFit(public_path() . 'static\\file_template\\出口-物流相关-报关单.xlsx'));
        // 获取工作表
        $sheet = $spreadsheet->getSheet(0);
        $index = 38;
        if(count($goods) > 1){
//            $sheet->insertNewRowBefore($index + 1, count($goods) - 1);
        }
        $goods_name = "";
        foreach($goods as $val){
//            $sheet->mergeCells("D{$index}:F{$index}");
//            $sheet->setCellValue('D' . $index, $val['name']); // 名称
            $goods_name .= $val['name'] . '&';
            $index++;
        }
        $goods_name = rtrim($goods_name, '&');
        $sign_date = empty($information['sign_date']) ? '未设置签约日期' : date('ymd', strtotime($information['sign_date']));
        // 保存修改后的Excel文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); // 保存为Excel 2007及以上版本的文件
        $filename = '报关单(' . $goods_name . ') - ' . $sign_date . '.xlsx';
        $filepath = $dir . $filename;
        $writer->save($filepath); // 修改为你想保存的文件名
        return $filename;
    }

    /**
     * 出口 - 物流相关 - 订舱单
     */
    public function generateCkWlDcd($param, $dir){
        $information = db::name('customer_order_information')->where(['order_id' => $param['order_id']])->find();
        $goods = db::name('customer_order_goods')->where(['order_id' => $param['order_id']])->select();
        // 加载Excel文件
        $spreadsheet = IOFactory::load(Filesystem::fsFit(public_path() . 'static\\file_template\\出口-物流相关-订舱单.xlsx'));
        // 获取工作表
        $sheet = $spreadsheet->getSheet(0);
        $index = 38;
        if(count($goods) > 1){
            $sheet->insertNewRowBefore($index + 1, count($goods) - 1);
        }
        $goods_name = "";
        foreach($goods as $val){
            $sheet->mergeCells("D{$index}:F{$index}");
            $sheet->setCellValue('D' . $index, $val['name']); // 名称
            $goods_name .= $val['name'] . '&';
            $index++;
        }
        $goods_name = rtrim($goods_name, '&');
        $sign_date = empty($information['sign_date']) ? '未设置签约日期' : date('ymd', strtotime($information['sign_date']));
        // 保存修改后的Excel文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); // 保存为Excel 2007及以上版本的文件
        $filename = '订舱单(' . $goods_name . ') - ' . $sign_date . '.xlsx';
        $filepath = $dir . $filename;
        $writer->save($filepath); // 修改为你想保存的文件名
        return $filename;
    }

    /**
     * 出口 - 客户 - 合同
     */
    public function generateCkKhHt($param, $dir){
        $information = db::name('customer_order_information')->where(['order_id' => $param['order_id']])->find();
        $goods = db::name('customer_order_goods')->where(['order_id' => $param['order_id']])->select();
        // 加载Excel文件
        $spreadsheet = IOFactory::load(Filesystem::fsFit(public_path() . 'static\\file_template\\出口-客户-合同.xlsx'));
        // 获取工作表
        $sheet = $spreadsheet->getSheet(0);
        if(!empty($information['sign_date'])) {
            $sheet->setCellValue('G3', $information['sign_date']);
        }
        if(!empty($information['contract_number'])) {
            $sheet->setCellValue('G4', $information['contract_number']);
        }



        $index = 8;
        if(count($goods) > 1){
            $sheet->insertNewRowBefore($index + 1, count($goods) - 1);
        }
        $goods_name = "";
        foreach($goods as $val){
            $sheet->mergeCells("A{$index}:B{$index}");
            $sheet->mergeCells("C{$index}:D{$index}");
            $sheet->mergeCells("E{$index}:F{$index}");
            $sheet->mergeCells("G{$index}:H{$index}");
            $sheet->setCellValue('A' . $index, $val['name']); // 名称
            // 成交单价
            if(empty($val['deal_unit_amount'])){
                $sheet->setCellValue('E' . $index, '');
            }else{
                $deal_unit_amount = $val['deal_unit_amount'] / 100;
                $sheet->setCellValue('E' . $index, $deal_unit_amount);
            }
            // 成交总价
            if(empty($val['deal_amount'])){
                $sheet->setCellValue('G' . $index, '');
            }else{
                $deal_amount = $val['deal_amount'] / 100;
                $sheet->setCellValue('G' . $index, $deal_amount);
            }
            $goods_name .= $val['name'] . '&';
            $index++;
        }

        if(!empty($information['customer_corp_name'])) {
            $sheet->setCellValue('B4', $information['customer_corp_name']);
            $sheet->setCellValue('B' . $index + 7, $information['customer_corp_name']);
        }
        if(!empty($information['customer_address'])) {
            $sheet->setCellValue('B' . $index + 8, $information['customer_address']);
            $sheet->getStyle('B' . ($index + 8))->getAlignment()->setWrapText(true);
        }
        if(!empty($information['customer_contact'])) {
            $sheet->setCellValue('B' . $index + 10, $information['customer_contact']);
        }

        $goods_name = rtrim($goods_name, '&');
        $sign_date = empty($information['sign_date']) ? '未设置签约日期' : date('ymd', strtotime($information['sign_date']));
        // 保存修改后的Excel文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); // 保存为Excel 2007及以上版本的文件
        $filename = '客户合同(' . $goods_name . ') - ' . $sign_date . '.xlsx';
        $filepath = $dir . $filename;
        $writer->save($filepath); // 修改为你想保存的文件名
        return $filename;
    }

    /**
     * 出口 - 供应商 - 合同
     */
    public function generateCkGysHt($param, $dir){
        $information = db::name('customer_order_information')->where(['order_id' => $param['order_id']])->find();
        $goods = db::name('customer_order_goods')->where(['order_id' => $param['order_id']])->select();
        // 加载Excel文件
        $spreadsheet = IOFactory::load(Filesystem::fsFit(public_path() . 'static\\file_template\\出口-供应商-合同.xlsx'));
        // 获取工作表
        $sheet = $spreadsheet->getSheet(0);
        if(!empty($information['sign_date'])) {
            $sheet->setCellValue('G3', $information['sign_date']);
        }
        if(!empty($information['contract_number'])) {
            $sheet->setCellValue('G4', $information['contract_number']);
        }
        $index = 8;
        if(count($goods) > 1){
            $sheet->insertNewRowBefore($index + 1, count($goods) - 1);
        }
        $goods_name = "";
        foreach($goods as $val){
            $sheet->setCellValue('A' . $index, $val['name']); // 名称
            // 成交单价
            if(empty($val['deal_unit_amount'])){
                $sheet->setCellValue('F' . $index, '');
            }else{
                $deal_unit_amount = $val['deal_unit_amount'] / 100;
                $sheet->setCellValue('F' . $index, $deal_unit_amount);
            }
            // 成交总价
            if(empty($val['deal_amount'])){
                $sheet->setCellValue('F' . $index, '');
            }else{
                $deal_amount = $val['deal_amount'] / 100;
                $sheet->setCellValue('F' . $index, $deal_amount);
            }
            $goods_name .= $val['name'] . '&';
            $index++;
        }

        $goods_name = rtrim($goods_name, '&');
        $sign_date = empty($information['sign_date']) ? '未设置签约日期' : date('ymd', strtotime($information['sign_date']));
        // 保存修改后的Excel文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); // 保存为Excel 2007及以上版本的文件
        $filename = '供应商合同(' . $goods_name . ') - ' . $sign_date . '.xlsx';
        $filepath = $dir . $filename;
        $writer->save($filepath); // 修改为你想保存的文件名
        return $filename;
    }


    /**
     * 出口 - 报关单
     */
    public function generateCustomesDeclaration($param, $dir){
        $goods = db::name('business_goods')->alias('g')->field($this->goodsField)
            ->leftJoin('hscode h', 'h.id=g.hscode_id')
            ->where(['g.no' => $param['no']])
            ->order('g.id asc')
            ->select();

        $goods_name = [];
        foreach($goods as $val){
            $goods_name[] = $val['name'];
        }

        if(!$goods_name) $this->error(__('未添加商品，无法生成报关单'));

        $goods_name = implode('&', $goods_name);
        $filename = '报关单(' . $goods_name . ')' . date('ymdHis') . '.xlsx';
        $filepath = $dir . $filename;
        $company = Config::get('company');

        $information = db::name('business_information')->where(['id' => $param['business_id']])->find();
        if(!$information){
            $information = db::name('business_information')->where(['no' => $param['no']])->order('id desc')->find();
        }




        $spreadsheet = new Spreadsheet(); // 创建文件
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体'); // 设置默认字体
        $spreadsheet->getDefaultStyle()->getFont()->setSize(10);
        $spreadsheet->getDefaultStyle()->getAlignment()->setVertical(Alignment::VERTICAL_CENTER); // 全局垂直居中

        $sheet = $spreadsheet->getActiveSheet(); // 选择第一个选项卡
        $sheet->setTitle('报关单'); //设置worksheet名称
        $sheet->getDefaultRowDimension()->setRowHeight(15.6); // 设置默认行高
        $sheet->getColumnDimension('A')->setWidth(5.55); // 设置列宽
        $sheet->getColumnDimension('B')->setWidth(6.68);
        $sheet->getColumnDimension('C')->setWidth(5.9);
        $sheet->getColumnDimension('D')->setWidth(20.3);
        $sheet->getColumnDimension('E')->setWidth(10.18);
        $sheet->getColumnDimension('F')->setWidth(13.55);
        $sheet->getColumnDimension('G')->setWidth(10.3);
        $sheet->getColumnDimension('H')->setWidth(6.05);
        $sheet->getColumnDimension('I')->setWidth(10);
        $sheet->getColumnDimension('J')->setWidth(6.43);
        $sheet->getColumnDimension('K')->setWidth(10);
        $sheet->getColumnDimension('L')->setWidth(8.3);
        $sheet->getColumnDimension('M')->setWidth(5.3);
        $sheet->getColumnDimension('N')->setWidth(5.42);
        $sheet->getColumnDimension('O')->setWidth(7.55);
        $sheet->getColumnDimension('P')->setWidth(5.42);
        $sheet->getColumnDimension('Q')->setWidth(5.55);
        $sheet->getColumnDimension('R')->setWidth(6.93);
        $sheet->getColumnDimension('S')->setWidth(7.8);

        $sheet->setCellValue('A1', '中华人民共和国海关出口货物报关单'); // 设置内容
        $sheet->getRowDimension(1)->setRowHeight(33); // 设置行高
        $sheet->getStyle('A1')->getFont()->setSize(20)->setBold(true); //字体大小
        $sheet->getStyle('A1')->getFont()->setBold(true); //字体加粗
        $sheet->mergeCells( 'A1:S1'); // 合并单元格
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); // 水平居中

        $sheet->setCellValue('A2', '预录入编号:');
        $sheet->getStyle('A2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->mergeCells( 'A2:B2'); // 合并单元格
        $sheet->mergeCells( 'C2:E2'); // 合并单元格
        $sheet->setCellValue('F2', '申报口岸:');
        $sheet->getStyle('F2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->mergeCells( 'G2:H2'); // 合并单元格
        $sheet->setCellValue('I2', '海关编号:');
        $sheet->getStyle('I2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->mergeCells( 'J2:N2'); // 合并单元格

        $sheet->setCellValue('A3', '境内发货人');
        $sheet->mergeCells( 'A3:B3'); // 合并单元格
        $sheet->mergeCells( 'C3:D3'); // 合并单元格
        $sheet->setCellValue('E3', '出境关别');
        $sheet->setCellValue('G3', '进口日期');
        $sheet->mergeCells( 'H3:J3'); // 合并单元格
        $sheet->setCellValue('K3', '申报日期');
        $sheet->mergeCells( 'L3:N3'); // 合并单元格
        $sheet->setCellValue('O3', '备案号');
        $sheet->mergeCells( 'P3:S3'); // 合并单元格
        $sheet->getRowDimension(4)->setRowHeight(25);

        $sheet->setCellValue('A4', $company['name_cn']);
        $sheet->mergeCells( 'A4:D4');
        $sheet->setCellValue('E4', '前海港区');
        $sheet->mergeCells( 'E4:F4');
        $sheet->setCellValue('G4', '当天');
        $sheet->mergeCells( 'G4:I4');
        $sheet->mergeCells( 'K4:N4');
        $sheet->mergeCells( 'O4:H4');

        $sheet->setCellValue('A5', '境外收货人');
        $sheet->setCellValue('C5', 'AEO：');
        $sheet->setCellValue('E5', '运输方式');
        $sheet->setCellValue('G5', '运输工具名称及航次号');
        $sheet->setCellValue('K5', '提运单号');
        $sheet->setCellValue('O5', '货物存放地点');
        $sheet->mergeCells( 'A5:B5');
        $sheet->mergeCells( 'G5:J5');
        $sheet->mergeCells( 'L5:N5');
        $sheet->mergeCells( 'O5:P5');
        $sheet->mergeCells( 'Q5:S5');

        $sheet->setCellValue('A6', $information['customer_corp_name']);


        $sheet->getRowDimension(7)->setRowHeight(10);

        $sheet->setCellValue('A8', 'Shipper');
        $sheet->getRowDimension(8)->setRowHeight(30);
        $sheet->getStyle('A8')->getFont()->setSize(16)->setBold(true);
        $sheet->getStyle('A8')->getFont()->setBold(true);
        $sheet->mergeCells( 'A8:F8');
        $sheet->getStyle('A8')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A8:F8')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('F2F2F2');

        $sheet->getRowDimension(9)->setRowHeight(10);

        $sheet->setCellValue('A10', 'Company Name:');
        $sheet->setCellValue('B10', $company['name_en']);
        $sheet->getStyle('A10')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('B10')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        $sheet->mergeCells('B10:F10');

        $sheet->setCellValue('A11', 'Address:');
        $sheet->setCellValue('B11', $company['address_en']);
        $sheet->getStyle('A11')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('B11')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        $sheet->mergeCells('B11:F11');

        $sheet->setCellValue('A12', 'Tel:');
        $sheet->setCellValue('B12', $company['phone']);
        $sheet->getStyle('A12')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('B12')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        $sheet->mergeCells('B12:F12');

        $sheet->getRowDimension(13)->setRowHeight(10);

        $sheet->setCellValue('A14', 'Delivery To');
        $sheet->getRowDimension(14)->setRowHeight(30);
        $sheet->getStyle('A14')->getFont()->setSize(16)->setBold(true);
        $sheet->getStyle('A14')->getFont()->setBold(true);
        $sheet->mergeCells('A14:F1');
        $sheet->getStyle('A14')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A14:F14')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('F2F2F2');

        $sheet->getRowDimension(15)->setRowHeight(10);

        $sheet->setCellValue('A16', 'Company Name:');
        $sheet->setCellValue('B16', $information['customer_corp_name']);
        $sheet->getStyle('A16')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('B16')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        $sheet->mergeCells('B16:F16');

        $sheet->setCellValue('A17', 'Address:');
        $sheet->setCellValue('B17', $information['customer_address']);
        $sheet->getStyle('A17')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('B17')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        $sheet->mergeCells('B17:F17');

        $sheet->setCellValue('A18', 'Tel:');
        $sheet->setCellValue('B18', $information['customer_contact']);
        $sheet->getStyle('A18')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('B18')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        $sheet->mergeCells('B18:F18');

        $sheet->getRowDimension(19)->setRowHeight(10); // 收货人结束的间隔行


        $sheet->setCellValue('A20', "Marks and numbers" . "\n" . "nof pkgs");
        $sheet->getStyle('A20')->getAlignment()->setWrapText(true); // 设置样式为自动换行
        $sheet->getRowDimension(20)->setRowHeight(35);
        $sheet->setCellValue('B20', 'Description of Goods');
        $sheet->setCellValue('C20', 'N.WEIGHT');
        $sheet->setCellValue('D20', 'G.WEIGHT');
        $sheet->setCellValue('E20', 'QUANTITY');
        $sheet->setCellValue('F20', 'DIMENSION');
        $sheet->getStyle('A20:F20')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); // 水平居中
        $sheet->getStyle('A20:F20')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN); //设置边框 - 细

        $index = 21;
        $net_weight = 0;
        $gross_weight = 0;
        foreach($goods as $key => $val){
            $sheet->getStyle("A{$index}:F{$index}")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN); //设置边框 - 细
            $sheet->setCellValue('A' . $index, $val['hscode']);
            $sheet->setCellValue('B' . $index, $val['name_en']);
            if(!empty($val['net_weight'])){
                $sheet->setCellValue('C' . $index, $val['net_weight'] . 'KG');
            }
            if(!empty($val['gross_weight'])){
                $sheet->setCellValue('D' . $index, $val['gross_weight'] . 'KG');
            }

            $sheet->setCellValue('E' . $index, $val['pack_qty']);
            $sheet->getStyle('A' . $index . ':F' . $index)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); // 水平居中
            $index++;
            $net_weight += $val['net_weight'];
            $gross_weight += $val['gross_weight'];
        }

        $sheet->getStyle("A{$index}:F{$index}")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN); //设置边框 - 细
        $sheet->getStyle('A' . $index . ':F' . $index)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); // 水平居中
        $sheet->setCellValue('B' . $index, 'TOTAL');
        if($net_weight > 0){
            $sheet->setCellValue('C' . $index, $net_weight . 'KG');
        }
        if($gross_weight > 0){
            $sheet->setCellValue('D' . $index, $gross_weight . 'KG');
        }




        // 创建一个图片对象
        $drawing = new Drawing();
        $drawing->setName('公章');
        $drawing->setDescription('公章');
        $drawing->setPath(Filesystem::fsFit(public_path() . 'static\\images\\company\\official_seal.png')); // 设置图片的路径
        $drawing->setCoordinates('F10'); // 设置图片在工作表中的位置
        $drawing->setOffsetX(25); // 设置图片横向偏移量
        $drawing->setOffsetY(-7); // 设置图片纵向偏移量

        // 设置图片的宽度和高度
        $drawing->setWidth(100);
        $drawing->setHeight(100);

        // 将图片添加到工作表
        //        $sheet->addDrawing($drawing);
        $drawing->setWorksheet($sheet); // 设置工作表

        //        echo $index;die;

        $writer = IOFactory::createWriter($spreadsheet, 'Xls'); // 写入数据
        $writer->save($filepath); // 保存文件
        return $filename;
    }


    /**
     * 出口 - 箱单
     */
    public function generatePackingList($param, $dir){
        $goods = db::name('customer_order_goods')->alias('g')->field($this->goodsField)
            ->leftJoin('hscode h', 'h.id=g.hscode_id')
            ->where(['g.order_id' => $param['order_id']])
            ->order('g.id asc')
            ->select();

        $goods_name = [];
        foreach($goods as $val){
            $goods_name[] = $val['name'];
        }

        if(!$goods_name) $this->error(__('未添加商品，无法生成装箱单'));

        $goods_name = implode('&', $goods_name);
        $filename = 'PACKING LIST(' . $goods_name . ') - ' . date('ymd') . '.xlsx';
        $filepath = $dir . $filename;
        $company = Config::get('company');

        $information = db::name('customer_order_information')->where(['order_id' => $param['order_id']])->find();


        $spreadsheet = new Spreadsheet(); // 创建文件
        $spreadsheet->getDefaultStyle()->getFont()->setName('宋体'); // 设置默认字体
        $spreadsheet->getDefaultStyle()->getAlignment()->setVertical(Alignment::VERTICAL_CENTER); // 全局垂直居中

        $sheet = $spreadsheet->getActiveSheet(); // 选择第一个选项卡
        $sheet->setTitle('PACKING LIST'); //设置worksheet名称
        $sheet->getDefaultRowDimension()->setRowHeight(22); // 设置默认行高
        $sheet->getColumnDimension('A')->setWidth(20); // 设置列宽
        $sheet->getColumnDimension('B')->setWidth(23);
        $sheet->getColumnDimension('C')->setWidth(12);
        $sheet->getColumnDimension('D')->setWidth(12);
        $sheet->getColumnDimension('E')->setWidth(12);
        $sheet->getColumnDimension('F')->setWidth(20);

        $sheet->setCellValue('A1', $company['name_en']); // 设置内容
        $sheet->getRowDimension(1)->setRowHeight(38); // 设置行高
        $sheet->getStyle('A1')->getFont()->setSize(16)->setBold(true); //字体大小
        $sheet->getStyle('A1')->getFont()->setBold(true); //字体加粗
        $sheet->mergeCells( 'A1:F1'); // 合并单元格
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); // 水平居中
        $sheet->getStyle('A1:F1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('F2F2F2'); // 设置背景色
        $sheet->getStyle('A1:F1')->getBorders()->getBottom()->setBorderStyle(Border::BORDER_THIN); //设置下边框细

        $sheet->setCellValue('A2', 'PACKING LIST');
        $sheet->getRowDimension(2)->setRowHeight(30);
        $sheet->getStyle('A2')->getFont()->setSize(16)->setBold(true);
        $sheet->getStyle('A2')->getFont()->setBold(true);
        $sheet->mergeCells( 'A2:F2');
        $sheet->getStyle('A2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A2:F2')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('F2F2F2');

        $sheet->getRowDimension(3)->setRowHeight(10);

        $sheet->getRowDimension(4)->setRowHeight(24);
        $sheet->setCellValue('A4', 'Port of loading:');
        $sheet->getStyle('A4')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->setCellValue('B4', 'China');
        $sheet->getStyle('B4')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

        $sheet->setCellValue('E4', 'Date:');
        $sheet->getStyle('E4')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        if(!empty($information['sign_date'])){
            $sheet->setCellValue('F4', $information['sign_date']);
        }
        $sheet->getStyle('F4')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

        $information['destination'] = empty($information['destination']) ? '' : __($information['destination'], [], 'en');
        $sheet->setCellValue('A5', 'Final Destination:');
        $sheet->setCellValue('B5', $information['destination']);
        $sheet->getStyle('A5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('B5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

        $sheet->setCellValue('E5', 'Invoice  No.:');
        if(!empty($information['invoice_number'])){
            $sheet->setCellValue('F5', $information['invoice_number']);
        }
        $sheet->getStyle('E5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('F5')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

        $sheet->setCellValue('A6', 'Carrier:');
        $sheet->setCellValue('B6', 'Sailing on or about');
        $sheet->getStyle('A6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('B6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

        $sheet->setCellValue('E6', 'Contract No.:');
        if(!empty($information['contract_number'])){
            $sheet->setCellValue('F6', $information['contract_number']);
        }
        $sheet->getStyle('E6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('F6')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);

        $sheet->getRowDimension(7)->setRowHeight(10);

        $sheet->setCellValue('A8', 'Shipper');
        $sheet->getRowDimension(8)->setRowHeight(30);
        $sheet->getStyle('A8')->getFont()->setSize(16)->setBold(true);
        $sheet->getStyle('A8')->getFont()->setBold(true);
        $sheet->mergeCells( 'A8:F8');
        $sheet->getStyle('A8')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A8:F8')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('F2F2F2');

        $sheet->getRowDimension(9)->setRowHeight(10);

        $sheet->setCellValue('A10', 'Company Name:');
        $sheet->setCellValue('B10', $company['name_en']);
        $sheet->getStyle('A10')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('B10')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        $sheet->mergeCells('B10:F10');

        $sheet->setCellValue('A11', 'Address:');
        $sheet->setCellValue('B11', $company['address_en']);
        $sheet->getStyle('A11')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('B11')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        $sheet->mergeCells('B11:F11');

        $sheet->setCellValue('A12', 'Tel:');
        $sheet->setCellValue('B12', $company['phone']);
        $sheet->getStyle('A12')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('B12')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        $sheet->mergeCells('B12:F12');

        $sheet->getRowDimension(13)->setRowHeight(10);

        $sheet->setCellValue('A14', 'Delivery To');
        $sheet->getRowDimension(14)->setRowHeight(30);
        $sheet->getStyle('A14')->getFont()->setSize(16)->setBold(true);
        $sheet->getStyle('A14')->getFont()->setBold(true);
        $sheet->mergeCells('A14:F1');
        $sheet->getStyle('A14')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A14:F14')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('F2F2F2');

        $sheet->getRowDimension(15)->setRowHeight(10);

        $sheet->setCellValue('A16', 'Company Name:');
        $sheet->setCellValue('B16', $information['customer_corp_name']);
        $sheet->getStyle('A16')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('B16')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        $sheet->mergeCells('B16:F16');

        $sheet->setCellValue('A17', 'Address:');
        $sheet->setCellValue('B17', $information['customer_address']);
        $sheet->getStyle('A17')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('B17')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        $sheet->mergeCells('B17:F17');

        $sheet->setCellValue('A18', 'Tel:');
        $sheet->setCellValue('B18', $information['customer_contact']);
        $sheet->getStyle('A18')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('B18')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        $sheet->mergeCells('B18:F18');

        $sheet->getRowDimension(19)->setRowHeight(10); // 收货人结束的间隔行


        $sheet->setCellValue('A20', "Marks and numbers" . "\n" . "nof pkgs");
        $sheet->getStyle('A20')->getAlignment()->setWrapText(true); // 设置样式为自动换行
        $sheet->getRowDimension(20)->setRowHeight(35);
        $sheet->setCellValue('B20', 'Description of Goods');
        $sheet->setCellValue('C20', 'N.WEIGHT');
        $sheet->setCellValue('D20', 'G.WEIGHT');
        $sheet->setCellValue('E20', 'QUANTITY');
        $sheet->setCellValue('F20', 'DIMENSION');
        $sheet->getStyle('A20:F20')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); // 水平居中
        $sheet->getStyle('A20:F20')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN); //设置边框 - 细

        $index = 21;
        $net_weight = 0;
        $gross_weight = 0;
        foreach($goods as $key => $val){
            $sheet->getStyle("A{$index}:F{$index}")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN); //设置边框 - 细
            $sheet->setCellValue('A' . $index, $val['hscode']);
            $sheet->setCellValue('B' . $index, $val['name_en']);
            if(!empty($val['net_weight'])){
                $sheet->setCellValue('C' . $index, $val['net_weight'] . 'KG');
            }
            if(!empty($val['gross_weight'])){
                $sheet->setCellValue('D' . $index, $val['gross_weight'] . 'KG');
            }

            $sheet->setCellValue('E' . $index, $val['pack_qty']);
            $sheet->getStyle('A' . $index . ':F' . $index)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); // 水平居中
            $index++;
            $net_weight += $val['net_weight'];
            $gross_weight += $val['gross_weight'];
        }

        $sheet->getStyle("A{$index}:F{$index}")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN); //设置边框 - 细
        $sheet->getStyle('A' . $index . ':F' . $index)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); // 水平居中
        $sheet->setCellValue('B' . $index, 'TOTAL');
        if($net_weight > 0){
            $sheet->setCellValue('C' . $index, $net_weight . 'KG');
        }
        if($gross_weight > 0){
            $sheet->setCellValue('D' . $index, $gross_weight . 'KG');
        }




        // 创建一个图片对象
        $drawing = new Drawing();
        $drawing->setName('公章');
        $drawing->setDescription('公章');
        $drawing->setPath(Filesystem::fsFit(public_path() . 'static\\images\\company\\official_seal.png')); // 设置图片的路径
        $drawing->setCoordinates('F10'); // 设置图片在工作表中的位置
        $drawing->setOffsetX(25); // 设置图片横向偏移量
        $drawing->setOffsetY(-7); // 设置图片纵向偏移量

        // 设置图片的宽度和高度
        $drawing->setWidth(100);
        $drawing->setHeight(100);

        // 将图片添加到工作表
//        $sheet->addDrawing($drawing);
        $drawing->setWorksheet($sheet); // 设置工作表

        $writer = IOFactory::createWriter($spreadsheet, 'Xls'); // 写入数据
        $writer->save($filepath); // 保存文件
        return $filename;
    }

    /**
     * 出口 - 客户 - CI&箱单
     */
    public function generateCustomesCiPk($param, $dir){
        $information = db::name('customer_order_information')->where(['order_id' => $param['order_id']])->find();
        $goods = db::name('customer_order_goods')->where(['order_id' => $param['order_id']])->select();
        // 加载Excel文件
        $spreadsheet = IOFactory::load(Filesystem::fsFit(public_path() . 'static\\file_template\\出口-客户-CI&箱单.xlsx'));
        // 获取工作表
        $sheet = $spreadsheet->getSheet(0);
        if(!empty($information['sign_date'])) {
            $sheet->setCellValue('B4', $information['sign_date']);
        }
        if(!empty($information['invoice_number'])) {
            $sheet->setCellValue('B5', $information['invoice_number']);
        }
        if(!empty($information['contract_number'])) {
            $sheet->setCellValue('B6', $information['contract_number']);
        }
        if(!empty($information['trade_country'])) {
            $sheet->setCellValue('G4', __($information['trade_country'])); // 装货港
        }
        $sheet->setCellValue('G5', ''); // 国内来源
        $sheet->setCellValue('G6', ''); // 承运人

        if(!empty($information['customer_corp_name'])) {
            $sheet->setCellValue('B16', $information['customer_corp_name']);
        }
        if(!empty($information['customer_address'])) {
            $sheet->setCellValue('B17', $information['customer_address']);
        }
        if(!empty($information['customer_contact'])) {
            $sheet->setCellValue('B18', $information['customer_contact']);
        }

        if(empty($information['trade_term'])) {
            $sheet->setCellValue('E20', 'PRICE');
            $sheet->setCellValue('F20', 'TOTAL PRICE');
        }else{
            $sheet->setCellValue('E20', $information['trade_term'] . ' PRICE');
            $sheet->setCellValue('F20', $information['trade_term'] . "\n" . 'TOTAL PRICE');
        }

        $index = 21;
        if(count($goods) > 1){
            $sheet->insertNewRowBefore($index + 1, count($goods) - 1);
        }
        $goods_name = "";
        foreach($goods as $val){
            if(empty($val['hscode_id'])){
                $hscode_result = null;
            }else{
                $hscode_result = db::name('hscode')->where(['id' => $val['hscode_id']])->find();
            }
            if($hscode_result){
                $sheet->setCellValue('A' . $index, $hscode_result['hscode']);
            }else{
                $sheet->setCellValue('A' . $index, '');
            }
            $sheet->setCellValue('B' . $index, $val['name_en']);
            $sheet->setCellValue('C' . $index, ''); // 数量
            $sheet->setCellValue('D' . $index, ''); // 单位
            if(empty($val['deal_unit_amount'])){ // 成交单价
                $sheet->setCellValue('E' . $index, '');
            }else{
                $deal_unit_amount = $val['deal_unit_amount'] / 100;
                $sheet->setCellValue('E' . $index, $deal_unit_amount);
            }
            if(empty($val['deal_amount'])){ // 成交总价
                $sheet->setCellValue('F' . $index, '');
            }else{
                $deal_amount = $val['deal_amount'] / 100;
                $sheet->setCellValue('F' . $index, $deal_amount);
            }
            $goods_name .= $val['name'] . '&';
            $index++;
        }
        $sheet->setCellValue('C' . $index, ''); // 总数量
        $sheet->setCellValue('E' . $index, '');
        $sheet->setCellValue('F' . $index, ''); // 总金额


        $sheet = $spreadsheet->getSheet(1);

        if(!empty($information['sign_date'])) {
            $sheet->setCellValue('B4', $information['sign_date']);
        }
        if(!empty($information['invoice_number'])) {
            $sheet->setCellValue('B5', $information['invoice_number']);
        }
        if(!empty($information['contract_number'])) {
            $sheet->setCellValue('B6', $information['contract_number']);
        }
        if(!empty($information['trade_country'])) {
            $sheet->setCellValue('G4', __($information['trade_country'])); // 装货港
        }
        $sheet->setCellValue('G5', ''); // 国内来源
        $sheet->setCellValue('G6', ''); // 承运人

        if(!empty($information['customer_corp_name'])) {
            $sheet->setCellValue('B16', $information['customer_corp_name']);
        }
        if(!empty($information['customer_address'])) {
            $sheet->setCellValue('B17', $information['customer_address']);
        }
        if(!empty($information['customer_contact'])) {
            $sheet->setCellValue('B18', $information['customer_contact']);
        }


        $index = 21;
        if(count($goods) > 1){
            $sheet->insertNewRowBefore($index + 1, count($goods) - 1);
        }
        foreach($goods as $val){
            if(empty($val['hscode_id'])){
                $hscode_result = null;
            }else{
                $hscode_result = db::name('hscode')->where(['id' => $val['hscode_id']])->find();
            }
            if($hscode_result){
                $sheet->setCellValue('A' . $index, $hscode_result['hscode']);
            }else{
                $sheet->setCellValue('A' . $index, '');
            }
            $sheet->setCellValue('B' . $index, $val['name_en']);
            $sheet->setCellValue('C' . $index, ''); // 数量
            $sheet->setCellValue('D' . $index, ''); // 单位
            $index++;
        }
        $sheet->setCellValue('C' . $index, ''); // 总数量
        $goods_name = rtrim($goods_name, '&');
        $sign_date = empty($information['sign_date']) ? '未设置签约日期' : date('ymd', strtotime($information['sign_date']));
        // 保存修改后的Excel文件
        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx'); // 保存为Excel 2007及以上版本的文件
        $filename = 'CI&箱单(' . $goods_name . ') - ' . $sign_date . '.xlsx';
        $filepath = $dir . $filename;
        $writer->save($filepath); // 修改为你想保存的文件名
        return $filename;
    }





















}